﻿Imports System.Data.SqlClient
Public Class dbUser
    Dim oEncript As New Crypt
    Dim Var As New varDeclare
    Dim dbSystem As New dbSystem

    Public Function CheckLogin(usrLoginID As String, usrLoginPassword As String) As String
        Dim rs As String = ""
        If (oEncript.EncryptMD5(usrLoginID) <> Var.Admin(0)) Then
            Dim connection As SqlConnection
            Dim command As New SqlCommand
            Dim ds As New DataSet
            usrLoginPassword = oEncript.EncryptMD5(usrLoginPassword)
            connection = New SqlConnection(Var.ConnString)
            connection.Open()
            command.Connection = connection
            command.CommandType = CommandType.Text
            command.CommandText = "SELECT usrGenID FROM tblUser WHERE usrLoginID = @usrLoginID AND usrLoginPassword = @usrLoginPassword"
            command.Parameters.Add("@usrLoginID", SqlDbType.VarChar).Value = usrLoginID
            command.Parameters.Add("@usrLoginPassword", SqlDbType.VarChar).Value = usrLoginPassword
            Dim result As Object = command.ExecuteScalar()
            connection.Close()
            rs = Convert.ToString(result)
        Else
            If (oEncript.EncryptMD5(usrLoginPassword) = Var.Admin(1)) Then
                rs = "SuperAdmin"
            End If
        End If
        Return rs
    End Function

    Public Function GetLoginUserList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT tu.usrGenID, tu.usrLoginID 'Login ID', tl.logTime 'Last Update' FROM tblUser tu WITH(NOLOCK) LEFT JOIN tblLog tl WITH(NOLOCK) ON tl.logGenID = tu.logGenID"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetModulePermissionList(UserID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        Dim ListModule As String = ""
        If (Var.ActiveModule <> "") Then
            ListModule = " AND ModuleID IN ('" & Var.ActiveModule & "')"
        End If
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT Module FROM v_ModulePermission WHERE UserID = @usrGenID" & ListModule
        command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = UserID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetFormPermissionList(UserID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        Dim ListForm As String = ""
        If (Var.ActiveForm <> "") Then
            ListForm = " AND FormID IN ('" & Var.ActiveForm & "')"
        End If
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM v_FormPermission WHERE UserID = @usrGenID" & ListForm
        command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = UserID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetFormPermission(UserID As String, FormName As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT perOpen O, perInsert I, perUpdate U,perDelete D,perPrint P FROM v_FormPermission WITH(NOLOCK) WHERE UserID = @usrGenID AND FormName = @formName"
        command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@formName", SqlDbType.VarChar).Value = FormName
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function LoginUserManagement(Type As String, usrGenID As String, usrLoginID As String, usrLoginPassword As String, empGenID As String, UserID As String) As String
        Dim rs As String = ""
        If (oEncript.EncryptMD5(usrLoginID) <> Var.Admin(0)) Then
            Dim connection As SqlConnection
            Dim command As New SqlCommand
            Dim ds As New DataSet
            usrLoginPassword = oEncript.EncryptMD5(usrLoginPassword)
            connection = New SqlConnection(Var.ConnString)
            connection.Open()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "proc_LoginUserManagement"
            command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
            command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = usrGenID
            command.Parameters.Add("@usrLoginID", SqlDbType.VarChar).Value = usrLoginID
            If (usrLoginPassword <> "D4-1D-8C-D9-8F-00-B2-04-E9-80-09-98-EC-F8-42-7E") Then
                command.Parameters.Add("@usrLoginPassword", SqlDbType.VarChar).Value = usrLoginPassword
            Else
                command.Parameters.Add("@usrLoginPassword", SqlDbType.VarChar).Value = DBNull.Value
            End If
            command.Parameters.Add("@empGenID", SqlDbType.VarChar).Value = empGenID
            command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID
            Dim result As Object = command.ExecuteScalar()
            connection.Close()
            rs = Convert.ToString(result)
        Else
            rs = "Failed, Restricted Login ID"
        End If
        Return rs
    End Function

    Public Function GetUserLoginDetail(usrGenID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetUserLoginDetail"
        command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = usrGenID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function SetUserPermission(usrGenID As String, dtPermission As DataTable, EntryID As String) As Boolean
        Dim result As Boolean = True
        Dim connection As SqlConnection
        Dim sqlTrans As SqlTransaction
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        sqlTrans = connection.BeginTransaction()
        Try
            For i = 0 To dtPermission.Rows.Count - 1
                If (result) Then
                    Dim command As New SqlCommand
                    command.Connection = connection
                    command.Transaction = sqlTrans
                    command.CommandType = CommandType.StoredProcedure
                    command.CommandText = "proc_SetUserPermission"
                    command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = usrGenID
                    command.Parameters.Add("@frmGenID", SqlDbType.VarChar).Value = dtPermission.Rows(i)("frmGenID").ToString()
                    command.Parameters.Add("@perOpen", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perOpen").ToString()
                    command.Parameters.Add("@perInsert", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perInsert").ToString()
                    command.Parameters.Add("@perUpdate", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perUpdate").ToString()
                    command.Parameters.Add("@perDelete", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perDelete").ToString()
                    command.Parameters.Add("@perPrint", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perPrint").ToString()
                    command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = EntryID
                    Dim x As Object = command.ExecuteScalar()
                    result = Convert.ToString(x)
                End If
            Next
            If (result) Then
                sqlTrans.Commit()
            Else : sqlTrans.Rollback()
            End If
        Catch ex As Exception
            sqlTrans.Rollback()
        End Try
        connection.Dispose()
        connection.Close()
        Return result
    End Function
End Class
